{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pathlib import Path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 数据整合：\n",
    "def data_concat():\n",
    "    # 提示用户输入文件夹路径：\n",
    "    folder_path = Path(input('请输入要处理文件的文件夹路径：'))\n",
    "    # 提取所有文件列表：\n",
    "    file_list = folder_path.glob('*merge.xlsx')\n",
    "    # 整合所有列表\n",
    "    df_list = []\n",
    "    for i in file_list:\n",
    "        if not i.name.startswith('~$'):\n",
    "            df = pd.read_excel(i)\n",
    "            df_list.append(df)\n",
    "    dfs = pd.concat(df_list,axis=0) # axis=0 代表上下合并，axis=1代表左右合并\n",
    "    # 提示用户需要提取哪列或者哪几列数据,name和date是必须添加的数据，因此也要进行整合\n",
    "    col_list = input('请输入要提取的列名，多个列名用空格间隔：').split(' ')\n",
    "    if ('name' in col_list)&('date' in col_list):\n",
    "        pass\n",
    "    elif 'name' in col_list:\n",
    "        col_list.append('date')\n",
    "    elif 'date' in col_list:\n",
    "        col_list.append(['name'])\n",
    "    else:\n",
    "        col_list.extend(['name','date'])\n",
    "    df = dfs[col_list]\n",
    "    df.set_index('name',inplace=True)\n",
    "    # 输出数据：\n",
    "    return df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 实现简单数据透视\n",
    "def pivot_data(df):\n",
    "    # 提示用户需要进行汇总的方法：\n",
    "    method_list = input('请输入要汇总的方法：求和,非重复计数,平均值,最大值,最小值,方差,标准差,多个方法以空格间隔：').split(' ')\n",
    "    table = pd.pivot_table(df,index='name',columns='date',fill_value=0)\n",
    "    for i in range(len(method_list)):\n",
    "        if method_list[i] == '求和':\n",
    "            table[method_list[i]] = table.sum(1)\n",
    "        elif method_list[i] == '平均值':\n",
    "            table[method_list[i]] = table.mean(1)\n",
    "            print(table[method_list[i]])\n",
    "        elif method_list[i] == '最大值':\n",
    "            table[method_list[i]] = table.max(1)\n",
    "        elif method_list[i] == '最小值':\n",
    "            table[method_list[i]] = table.min(1)\n",
    "        elif method_list[i] == '去重计数':\n",
    "            table[method_list[i]] = table.unique(1)\n",
    "        elif method_list[i] == '标准差':\n",
    "            table[method_list[i]] = table.std(1)\n",
    "        elif method_list[i] == '方差':\n",
    "            table[method_list[i]] = table.var(1)\n",
    "        else:\n",
    "            print('方法不存在')\n",
    "    table.to_excel('简单透视结果.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 具有时间频率透视功能的表格\n",
    "def freq_pivot(df):\n",
    "    # 对数据索引重置，使时间作为行索引\n",
    "    date_format= '''\n",
    "        可供选择日期频率：\n",
    "        每几个日历日：nD，例如'7D'\n",
    "        每几个工作日：nB, 例如'7B'\n",
    "        每月最后一个日历日：nM，例如'3M'\n",
    "        每月最后一个工作日：nBM，例如'3BM'\n",
    "        指定每周星期几算起：W-Mon\n",
    "        等等\n",
    "    '''\n",
    "    date_freq = input('请输入你想要的时间频率(输入时间需要小于现有数据时间跨度)：')\n",
    "    df['date'] = pd.to_datetime(df['date'])\n",
    "    df2 = df.reset_index().set_index('date')\n",
    "    method_list = input('请输入要汇总的方法：求和,平均值,最大值,最小值,方差,标准差,多个方法以空格间隔：').split(' ')\n",
    "    for i in range(len(method_list)):\n",
    "         if method_list[i] == '求和':\n",
    "            df3 = df2.groupby('name').resample(date_freq).sum()\n",
    "            df3.to_excel(f'时间频率为{date_freq}{method_list[i]}透视.xlsx')\n",
    "         elif method_list[i] == '平均值':\n",
    "            df3 = df2.groupby('name').resample(date_freq).mean()\n",
    "            df3.to_excel(f'时间频率为{date_freq}{method_list[i]}透视.xlsx')         \n",
    "         elif method_list[i] == '最大值':\n",
    "            df3 = df2.groupby('name').resample(date_freq).max()\n",
    "            df3.to_excel(f'时间频率为{date_freq}{method_list[i]}透视.xlsx') \n",
    "         elif method_list[i] == '最小值':\n",
    "            df3 = df2.groupby('name').resample(date_freq).min()\n",
    "            df3.to_excel(f'时间频率为{date_freq}{method_list[i]}透视.xlsx')\n",
    "         elif method_list[i] == '标准差':\n",
    "            df3 = df2.groupby('name').resample(date_freq).std()\n",
    "            df3.to_excel(f'时间频率为{date_freq}{method_list[i]}透视.xlsx')\n",
    "         elif method_list[i] == '方差':\n",
    "            df3 = df2.groupby('name').resample(date_freq).var()\n",
    "            df3.to_excel(f'时间频率为{date_freq}{method_list[i]}透视.xlsx')\n",
    "         else:\n",
    "            print('方法不存在')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 时间周期透视：\n",
    "def period_pivot(df):\n",
    "    date_format= '''\n",
    "        可供选择日期周期：\n",
    "        查看每连续几日的数据变化(如每连续10天)，请输入：10\n",
    "    '''\n",
    "    print(date_format)\n",
    "    df['date'] = pd.to_datetime(df['date'])\n",
    "    df2 = df.reset_index().set_index(['date','name'])\n",
    "    p = int(input('本功能为观测连续天数的某个数学指标的变化情况，请输入你想要的时间周期（不要超过数据时间跨度）：'))\n",
    "    method_list = input('请输入要汇总的方法：求和,平均值,最大值,最小值,方差,标准差,多个方法以空格间隔：').split(' ')\n",
    "    for i in range(len(method_list)):\n",
    "        if method_list[i] == '求和':\n",
    "            df3 = df2.unstack(level=1).rolling(window=p).sum()\n",
    "            df3.to_excel(f'时间周期为{p}{method_list[i]}透视.xlsx')\n",
    "        elif method_list[i] == '平均值':\n",
    "            df3 = df2.unstack(level=1).rolling(window=p).mean()\n",
    "            df3.to_excel(f'时间周期为{p}{method_list[i]}透视.xlsx')\n",
    "        elif method_list[i] == '最大值':\n",
    "            df3 = df2.unstack(level=1).rolling(window=10).max()\n",
    "            df3.to_excel(f'时间周期为{p}{method_list[i]}透视.xlsx')\n",
    "        elif method_list[i] == '最小值':\n",
    "            df3 = df2.unstack(level=1).rolling(window=p).min()\n",
    "            df3.to_excel(f'时间周期为{p}{method_list[i]}透视.xlsx')\n",
    "        elif method_list[i] == '标准差':\n",
    "            df3 = df2.unstack(level=1).rolling(window=p).std()\n",
    "            df3.to_excel(f'时间周期为{p}{method_list[i]}透视.xlsx')\n",
    "        elif method_list[i] == '方差':\n",
    "            df3 = df2.unstack(level=1).rolling(window=p).var()\n",
    "            df3.to_excel(f'时间周期为{p}{method_list[i]}透视.xlsx')\n",
    "        else:\n",
    "            print('方法不存在')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name\n",
      "*ST中孚     1.363333\n",
      "*ST中葡     1.721333\n",
      "*ST众泰     5.452000\n",
      "*ST兆新     2.712000\n",
      "*ST商城     3.594667\n",
      "           ...    \n",
      "鲁西化工     14.190000\n",
      "鲁阳节能     17.714000\n",
      "鸿路钢构      3.578667\n",
      "鸿远电子     16.606667\n",
      "黄河旋风      5.158667\n",
      "Name: 平均值, Length: 293, dtype: float64\n",
      "\n",
      "        可供选择日期周期：\n",
      "        查看每连续几日的数据变化(如每连续10天)，请输入：10\n",
      "    \n"
     ]
    }
   ],
   "source": [
    "df = data_concat()\n",
    "pivot_data(df)\n",
    "freq_pivot(df)\n",
    "period_pivot(df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "6f8ee94c255eb1f45edb80e83721093c1db1e2ea85447c0854292673b957abb8"
  },
  "kernelspec": {
   "display_name": "Python 3.8.5 64-bit ('base': conda)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
